﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using ModelLayer;

namespace DbLayer
{
    public class UserDb
    {
        private SqlUtility sqlUtility;
        private SqlConnection con;
        private AllergiesDb ADb;

        public UserDb()
        {
            sqlUtility = new SqlUtility();
            ADb = new AllergiesDb();
        }

        private int GetMaxId()
        {
            int count = 0;
            using (SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "select max (id) from Users";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    count = (int)cmd.ExecuteScalar();
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return count;
        }

        //Insert user in Database
        public int InsertUser(User _user)
        {
            int result = 0;
            User temUser = new User();
            const string query = "Insert into Users(userName, userPassword) values(@userName, @userPassword)";
            using(SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@userName", _user.UserName);
                    cmd.Parameters.AddWithValue("@userPassword", _user.UserPassword);
                    result += sqlUtility.ExecuteData(cmd, con);
                }
                con.Close();
                con.Dispose();
            }
            temUser = GetUserByUserNameAndPassword(_user.UserName, _user.UserPassword);
            const string query2 = "Insert into UserAllergies(userId, allergiesId) values(@userId, @allergiesId)";
           
                foreach (Allergies a in _user.UserAllergies)
                {
                    using (SqlConnection con = sqlUtility.GetCon())
                    {
                        con.Open();
                        using(SqlCommand cmd = new SqlCommand(query2, con))
                        {
                            cmd.Parameters.AddWithValue("@userId", temUser.Id);
                            cmd.Parameters.AddWithValue("@allergiesId", a.Id);
                            result += sqlUtility.ExecuteData(cmd, con);
                        }
                        con.Close();
                        con.Dispose();
                    }
                
            }
            return result;
        }
        //Build User Obj
        private User BuildUser(SqlDataReader _rdr)
        {
            User temUser = new User();

            temUser.Id = sqlUtility.GetSafeInt(_rdr, 0);
            temUser.UserName = sqlUtility.GetSafeString(_rdr, 1);
            temUser.UserPassword = sqlUtility.GetSafeString(_rdr, 2);
            //temUser.Allergies = GetUserAllergies(temUser.Id);

            return temUser;
        }
        public List<Allergies> GetUserAllergies(User _user)
        {
           
            List<Allergies> retList = new List<Allergies>();

            using(SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "select Allergies.id, Allergies.name, Allergies.summary from Allergies join userAllergies on Allergies.Id = userAllergies.allergiesId where userAllergies.userId = @userId";
                using(SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@userId", _user.Id);
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            Allergies allergy = ADb.BuildAllergies(rdr);
                            retList.Add(allergy);
                            
                         
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }

            return retList;
        }
        public User GetUser(string _UserPassword)
        {
            User temUser = null;

            using (con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "Select * from Users where UserPassword=@UserPassword";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@UserPassword", _UserPassword);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            temUser = BuildUser(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                const string query2 = "Select * from UserAllergies where UserId=@UserId";
                using (SqlCommand cmd = new SqlCommand(query2, con))
                {
                    cmd.Parameters.AddWithValue("@UserId", temUser.Id);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Allergies allergies = ADb.BuildAllergies(rdr);
                            temUser.UserAllergies.Add(allergies);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
       
                }


                con.Close();
                con.Dispose();
            }
            return temUser;
        }
        public User GetUserByUserNameAndPassword(string _username, string _userpassword)
        {
            User temUser = new User();
            using(SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                const string query = "select * from Users where userName= @userName AND userPassword=@userpassword";
                using(SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@userName", _username);
                    cmd.Parameters.AddWithValue("@userpassword", _userpassword);
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            temUser = BuildUser(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            temUser.UserAllergies = GetUserAllergies(temUser);
            return temUser;
        }

        public int UpdateUser(User _user)
        {
            int result = 0;
            User temUser = new User();
            const string query = "UPDATE Users SET username = @userName, userPassword = @userPasword WHERE Id = @userId"; 
            using (SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@userName", _user.UserName);
                    cmd.Parameters.AddWithValue("@userPassword", _user.UserPassword);
                    cmd.Parameters.AddWithValue("@userId", temUser.Id);                  
                    result += sqlUtility.ExecuteData(cmd, con);
                }
                con.Close();
                con.Dispose();
            }
            temUser = GetUserByUserNameAndPassword(_user.UserName, _user.UserPassword);
            const string query3 = "DELETE from UserAllergies WHERE userId = @userId";
            using (SqlConnection con = sqlUtility.GetCon())
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(query3, con))
                {
                    cmd.Parameters.AddWithValue("@userId", temUser.Id);
                    result += sqlUtility.ExecuteData(cmd, con);
                }
                con.Close();
                con.Dispose();
            }

            const string query2 = "Insert into UserAllergies(userId, allergiesId) values(@userId, @allergiesId)";

           
            foreach (Allergies a in _user.UserAllergies)
            {
                using (SqlConnection con = sqlUtility.GetCon())
                {
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand(query2, con))
                    {
                        cmd.Parameters.AddWithValue("@userId", temUser.Id);
                        cmd.Parameters.AddWithValue("@allergiesId", a.Id);
                        result += sqlUtility.ExecuteData(cmd, con);
                    }
                    con.Close();
                    con.Dispose();
                }

            }
            return result;       
        }


    }
}
